const Koa=require('koa')
const cors=require('koa-cors')
const bodyparser=require('koa-bodyparser')
const router=require('koa-router')()
const {Sequelize,DataTypes,Op}=require('sequelize')

const app=new Koa()

const sequelize=new Sequelize('demo03','sa','123456',{
    host:'localhost',
    dialect:'mssql'
})
sequelize.authenticate().then(()=>{
    try{
        console.log('成功');
    }catch(error){
        console.log('失败');
    }
})

const Class=sequelize.define('Class',{
    name:{
        type:DataTypes.STRING,
        allowNull:false
    }
})
const Students=sequelize.define('Students',{
    name:{
        type:DataTypes.STRING,
        allowNull:false
    },
    age:{
        type:DataTypes.INTEGER,
        allowNull:false
    },
    classId:{
        type:DataTypes.INTEGER,
        allowNull:false
    }
})

sequelize.sync({force:true}).then(async()=>{
    let studentClass=await Class.create({
        'name':'1班'
    })
    await Students.bulkCreate([
        {
            'name':'张三',
            'age':21,
            'classId':studentClass.id
        },
        {
            'name':'李四',
            'age':21,
            'classId':studentClass.id
        },
        {
            'name':'王五',
            'age':21,
            'classId':studentClass.id
        },
    ])
})

app.use(cors())
app.use(bodyparser())

//获取数据
router.get('/students',async(ctx,next)=>{
    let keyword=ctx.request.query.keyword || ''
    let stuClass=await Class.findAll()
    let arr=[]
    if(keyword && keyword.length>0){
        let {count,rows}=await Students.findAndCountAll({
            where:{
                [Op.or]:[
                    {name:keyword},
                    {age:isNaN(keyword)?0 :parseInt(keyword)},
                    {id:isNaN(keyword)?0 :parseInt(keyword)}
                ]
            },
            order:['id']
        })
        console.log(rows);
        rows.forEach(item=>{
            let sclass=stuClass.find(obj=>{
                return obj.id===item.classId
            })
            item.dataValues.className=sclass.dataValues.name
            arr.push(item)
        })
    }else{
        let {count,rows}=await Students.findAndCountAll()
        rows.forEach(item=>{
            let sclass=stuClass.find(obj=>{
                return obj.id===item.classId
            })
            item.dataValues.className=sclass.dataValues.name
            arr.push(item)
        })
    }
    ctx.body={
        code:1000,
        msg:'查询成功',
        data:arr
    }
}).post('/students',async(ctx)=>{
    let data=ctx.request.body
    data.classId=1
    let student=await Students.create(data)
    ctx.body={
        code:1000,
        msg:'添加成功',
        data:student
    }
}).put('/students/:id',async(ctx)=>{
    let id=ctx.request.params.id
    let data=ctx.request.body
    data.classId=1
    await Students.update(data,{
        where:{
            id:id
        }
    })
    let list=await Students.findAll({
        where:{
            id:id
        }
    })
    ctx.body={
        code:1000,
        msg:'修改成功',
        data:list
    }
}).delete('/students/:id',async(ctx)=>{
    let id=ctx.request.params.id
    await Students.destroy({
        where:{
            id:id
        }
    })
    ctx.body={
        code:1000,
        msg:'删除成功',
        data:id
    }
}).get('/students/:id',async(ctx)=>{
    let id=ctx.request.params.id
    let data=await Students.findAll({
        where:{
            id:id
        }
    })
    ctx.body={
        code:1000,
        msg:'查找成功',
        data:data
    }
})

app.use(router.routes())

app.listen(3000,()=>{
    console.log(`http://localhost:3000`);
})